package ut.data;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;
public class Database{
	public static Connection getConnection() throws NamingException, SQLException{
		DataSource ds = (DataSource)((new InitialContext()).lookup("java:/comp/env/jdbc/ut"));
		return ds.getConnection();
	}
	public static void executeDailyUpdate(){
		StringBuilder records = new StringBuilder("");
		Connection con = null;
		Statement stmt1 = null, stmt2 = null;
		ResultSet rs = null;
		try{
			con = getConnection();
			stmt1 = con.createStatement();
			stmt2 = con.createStatement();
			con.setAutoCommit(false);
			int updatedCount = 0;
			// 案主建立案件，但在案件逾期前沒有完成配對，系統設定媒合失敗，刪除案件申請紀錄
			records.append("媒合失敗案件編號：");
			long id;
			int employer, price;
			rs = stmt1.executeQuery("select id,employer,price from task where state=" + Task.MATCHING + " and now() > dead_time");
			while(rs.next()){
				id = rs.getLong("id");
				employer = rs.getInt("employer");
				price = rs.getInt("price");
				// 設定案件狀態
				if(stmt2.executeUpdate("update task set state=" + Task.DEAD + ",finish_time=now() where id=" + id) == 1){
					// 退款給案主
					stmt2.executeUpdate("update user set money=money+" + price + " where id=" + employer);
					// 清除相關的接案申請
					stmt2.executeUpdate("delete from application where id=" + id);
					// 建立交易紀錄
					stmt2.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + employer + "," + price + ",''," + Transaction.TYPE_DEAD_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
					// 加入訊息中
					records.append(id + ",");
					updatedCount++;
				}
			}
			// 譯者未完成稿件，且案主在案件逾期 7 天後未撤回案件，系統自動撤回：更新案件狀態、退款給案主
			records.append("<br/>系統撤回案件編號：");
			rs = stmt1.executeQuery("select id,employer,price from task where state=" + Task.MATCHED + " and TO_DAYS(now()) - TO_DAYS(task.dead_time) > 7");
			while(rs.next()){
				id = rs.getLong("id");
				employer = rs.getInt("employer");
				price = rs.getInt("price");
				// 設定案件狀態
				if(stmt2.executeUpdate("update task set state=" + Task.REVOKED + ",finish_time=now() where id=" + id) == 1){
					// 退款給案主
					stmt2.executeUpdate("update user set money=money+" + price + " where id=" + employer);
					// 建立交易紀錄
					stmt2.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + employer + "," + price + ",''," + Transaction.TYPE_REVOKE_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
					// 加入訊息中
					records.append(id + ",");
					updatedCount++;
				}
			}
			// 譯者完成稿件，但案主在 7 天內沒有進行審核，系統自動通過審核，案件成交
			records.append("<br/>系統成交案件編號：");
			int translator;
			rs = stmt1.executeQuery("select id,employer,translator,price from task where state=" + Task.TRANSLATED + " and TO_DAYS(now()) - TO_DAYS(task.translate_time) > 7");
			while(rs.next()){
				id = rs.getLong("id");
				translator = rs.getInt("translator");
				price = rs.getInt("price");
				// 設定案件狀態
				if(stmt2.executeUpdate("update task set state=" + Task.ACCEPTED + ",finish_time=now() where id=" + id) == 1){
					// 付款給譯者
					stmt2.executeUpdate("update user set money=money+" + price + " where id=" + translator);
					// 更新譯者紀錄
					stmt2.executeUpdate("update record set success=success+1 where id=" + translator);
					// 更新案主紀錄
					stmt2.executeUpdate("update record set task_success=task_success+1 where id=" + rs.getInt("employer"));
					// 建立交易紀錄
					stmt2.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + translator + "," + price + ",''," + Transaction.TYPE_ACCEPT_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
					// 加入訊息中
					records.append(id + ",");
					updatedCount++;
				}
			}
			con.commit();
			ut.service.Mail.sendDailyUpdateEmail(updatedCount, records.toString());
			return;
		}catch(NamingException e){
			e.printStackTrace();
			return;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return;
				}
			}
			return;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt1 != null){
					stmt1.close();
				}
				if(stmt2 != null){
					stmt2.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return;
			}
		}
	}
	public static int executeUpdate(String query){
		Connection con = null;
		Statement stmt = null;
		try{
			con = getConnection();
			stmt = con.createStatement();
			return stmt.executeUpdate(query);
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	public static boolean executeTransaction(String[] querys){
		Connection con = null;
		Statement stmt = null;
		try{
			con = getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			for(int i = 0; i < querys.length; i++){
				stmt.executeUpdate(querys[i]);
			}
			con.commit();
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
}